#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb, copy, json
from MySQLdb.cursors import DictCursor
from DBUtils.PooledDB import PooledDB
from datetime import date, datetime
from config import *

##### JSON格式化
class JSONclass(json.JSONEncoder):
  def default(self, obj):
      if isinstance(obj, datetime):
          return obj.strftime('%Y-%m-%d %H:%M:%S')
      elif isinstance(obj, date):
          return obj.strftime('%Y-%m-%d')
      else:
          return json.JSONEncoder.default(self, obj)

########## 数据库管理员

class DBManager():
    def __init__(self):
        self.pool = PooledDB(creator=MySQLdb, mincached=1, maxcached=20, use_unicode=True, charset='utf8', cursorclass=DictCursor, **db)
    def save_or_update(self, sql, pms):
        print 'sql:', sql
        print 'pms:', pms
        try:
            conn = self.pool.connection()
            cursor = conn.cursor()
            cursor.execute(sql, pms)
            ret = int(cursor.lastrowid)
            conn.commit()
            print 'ret:', ret
            return ret
        except Exception as e:
            print 'save_or_update Ex:', e
        finally:
            cursor.close()
            conn.close()
    def fetch_all(self, sql, pms=None):
        print 'sql:', sql
        print 'pms:', pms
        try:
            conn = self.pool.connection()
            cursor = conn.cursor()
            if pms is None:
                count = cursor.execute(sql)
            else:
                count = cursor.execute(sql, pms)
            if count > 0:
                ret = cursor.fetchall()
                ret = json.dumps(ret, cls=JSONclass)
                ret = json.loads(ret)
            else:
                ret = []
            conn.commit()
            print 'ret:', ret
            return ret
        except Exception as e:
            print 'fetch_all Ex:', e
        finally:
            cursor.close()
            conn.close()

##### DBManager
M = DBManager()

########## SQL生成工具
## 字典的键
def keys(row):
    return '`' + '`, `'.join(row.keys()) + '`'
## 字典的值占位符
def tmps(row):
    arr = []
    for x in xrange(0,len(row.keys())):
        arr.append('%s')
    return ', '.join(arr)
## 字典的值元组
def vals(row):
    return tuple(row.values())
## 字典除去id的更新SQL
def setks(row):
    r = copy.copy(row)
    if 'id' not in r:
        print '错误，id不存在'
    v = r['id']
    del r['id']
    return '`' + '`=%s, `'.join(r.keys()) + '`=%s'
## 字典除去id的更新值
def setvs(row):
    r = copy.copy(row)
    if 'id' not in r:
        print '错误，id不存在'
    v = r['id']
    del r['id']
    vs = r.values()
    vs.append(v)
    return tuple(vs)


########## 数据库表父类

class DBTable():
    #构造函数
    def __init__(self, tbname):
        self.tbname = tbname
    #基本SQL操作开头
    def _insert(self):
        return 'INSERT INTO `' + self.tbname + '` '
    def _replace(self):
        return 'REPLACE INTO `' + self.tbname + '` '
    def _delete(self):
        return 'DELETE FROM `' + self.tbname + '` '
    def _update(self):
        return 'UPDATE `' + self.tbname + '` SET '
    def _select(self):
        return 'SELECT * FROM `' + self.tbname + '` '
    #基本SQL操作
    def insert(self, row):
        if isinstance(row, dict): #字典转单条插入SQL语句
            sql = self._insert() + '(' + keys(row) + ') VALUES (' + tmps(row) + ')'
            pms = vals(row)
        elif isinstance(row, list): #列表转多条插入SQL语句
            vs = [] #占位
            vv = [] #值
            for x in row:
                vs.append(tmps(x))
                for y in x.values():
                    vv.append(y)
            sql = self._insert() + '(' + keys(row[0]) + ') VALUES (' + '), ('.join(vs) + ')'
            pms = tuple(vv)
        return M.save_or_update(sql, pms)
    def replace(self, row):
        if isinstance(row, dict): #字典转单条替换插入SQL语句
            sql = self._replace() + '(' + keys(row) + ') VALUES (' + tmps(row) + ')'
            pms = vals(row)
        elif isinstance(row, list): #列表转多条替换插入SQL语句
            vs = [] #占位
            vv = [] #值
            for x in row:
                vs.append(tmps(x))
                for y in x.values():
                    vv.append(y)
            sql = self._replace() + '(' + keys(row[0]) + ') VALUES (' + '), ('.join(vs) + ')'
            pms = tuple(vv)
        return M.save_or_update(sql, pms)
    def deletebyid(self, id):
        sql = self._delete() + 'WHERE `id`=%s'
        pms = (id, )
        return M.save_or_update(sql, pms)
    def updatebyid(self, row):
        sql =  self._update() + setks(row) + ' WHERE `id`=%s'
        pms = setvs(row)
        return M.save_or_update(sql, pms)
    def findbyid(self, id):
        sql = self._select() + 'WHERE `id`=%s'
        pms = (id, )
        return M.fetch_all(sql, pms)
    def findall(self):
        sql = self._select()
        return M.fetch_all(sql)
